/**
 * 增强的数据库连接池管理器
 * 解决SQLite数据库锁定问题，提供连接池、事务支持、健康检查和监控
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const EventEmitter = require('events');

class DatabasePool extends EventEmitter {
  constructor(dbPath, options = {}) {
    super();

    this.dbPath = dbPath;
    this.options = {
      maxConnections: options.maxConnections || 5,
      acquireTimeout: options.acquireTimeout || 30000, // 增加到30秒
      retryAttempts: options.retryAttempts || 3,
      retryDelay: options.retryDelay || 2000, // 增加重试延迟
      healthCheckInterval: options.healthCheckInterval || 30000, // 30秒
      connectionMaxAge: options.connectionMaxAge || 3600000, // 1小时
      enableMetrics: options.enableMetrics !== false,
      connectionCreationTimeout: options.connectionCreationTimeout || 15000, // 连接创建超时
      ...options
    };

    this.pool = [];
    this.activeConnections = 0;
    this.waitingQueue = [];
    this.connectionMetadata = new Map(); // 连接元数据
    this.startTime = Date.now(); // 启动时间
    this.metrics = {
      totalQueries: 0,
      successfulQueries: 0,
      failedQueries: 0,
      totalTransactions: 0,
      successfulTransactions: 0,
      failedTransactions: 0,
      averageQueryTime: 0,
      connectionCreated: 0,
      connectionDestroyed: 0
    };

    // 初始化连接池
    this.initializePool();

    // 启动健康检查
    if (this.options.healthCheckInterval > 0) {
      this.startHealthCheck();
    }
  }

  /**
   * 初始化连接池
   */
  async initializePool() {
    console.log(`初始化数据库连接池: ${this.dbPath}`);
    console.log(`最大连接数: ${this.options.maxConnections}`);

    // 验证数据库文件
    await this.validateDatabase();

    // 预创建一个连接进行测试
    try {
      const testConnection = await this.createConnection();
      await this.testConnection(testConnection);
      this.pool.push(testConnection);
      console.log('数据库连接池初始化成功');
      this.emit('poolInitialized');
    } catch (error) {
      console.error('数据库连接池初始化失败:', error);
      this.emit('poolInitializationFailed', error);
      throw error;
    }
  }

  /**
   * 验证数据库文件
   */
  async validateDatabase() {
    const dbDir = path.dirname(this.dbPath);

    // 确保数据库目录存在
    if (!fs.existsSync(dbDir)) {
      fs.mkdirSync(dbDir, { recursive: true });
    }

    // 检查数据库文件权限
    if (fs.existsSync(this.dbPath)) {
      try {
        fs.accessSync(this.dbPath, fs.constants.R_OK | fs.constants.W_OK);
      } catch (error) {
        throw new Error(`数据库文件权限不足: ${this.dbPath}`);
      }
    }
  }

  /**
   * 测试数据库连接
   */
  async testConnection(connection) {
    return new Promise((resolve, reject) => {
      connection.get('SELECT 1 as test', (err, row) => {
        if (err) {
          reject(new Error(`数据库连接测试失败: ${err.message}`));
        } else if (row && row.test === 1) {
          resolve(true);
        } else {
          reject(new Error('数据库连接测试返回异常结果'));
        }
      });
    });
  }

  /**
   * 启动健康检查
   */
  startHealthCheck() {
    this.healthCheckTimer = setInterval(async () => {
      try {
        await this.performHealthCheck();
      } catch (error) {
        console.error('健康检查失败:', error);
        this.emit('healthCheckFailed', error);
      }
    }, this.options.healthCheckInterval);

    console.log(`健康检查已启动，间隔: ${this.options.healthCheckInterval}ms`);
  }

  /**
   * 执行健康检查
   */
  async performHealthCheck() {
    const status = this.getStatus();

    // 检查连接池状态
    if (status.activeConnections > status.maxConnections) {
      this.emit('poolOverflow', status);
    }

    // 清理过期连接
    await this.cleanupExpiredConnections();

    // 测试池中的连接
    await this.testPoolConnections();

    this.emit('healthCheckCompleted', status);
  }

  /**
   * 创建新的数据库连接
   */
  createConnection() {
    return new Promise((resolve, reject) => {
      const connectionId = `conn_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;

      const db = new sqlite3.Database(this.dbPath, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => {
        if (err) {
          console.error('创建数据库连接失败:', err);
          this.updateMetrics('connectionCreationFailed');
          reject(err);
        } else {
          try {
            // 设置数据库配置
            db.configure('busyTimeout', 60000); // 增加到60秒超时

            // 只在第一个连接时设置全局PRAGMA，避免冲突
            if (this.connectionMetadata.size === 0) {
              this.setupGlobalPragmas(db, () => {
                this.finalizeConnection(db, connectionId, resolve);
              });
            } else {
              // 对于后续连接，只设置连接级别的配置
              this.setupConnectionPragmas(db, () => {
                this.finalizeConnection(db, connectionId, resolve);
              });
            }
          } catch (error) {
            console.error('数据库连接配置失败:', error);
            reject(error);
          }
        }
      });
    });
  }

  /**
   * 设置全局PRAGMA（仅第一个连接）
   */
  setupGlobalPragmas(db, callback) {
    const globalPragmas = [
      'PRAGMA journal_mode = WAL',
      'PRAGMA synchronous = NORMAL',
      'PRAGMA foreign_keys = ON',
      'PRAGMA wal_autocheckpoint = 1000',
      'PRAGMA encoding = "UTF-8"'
    ];

    let completed = 0;
    const total = globalPragmas.length;

    globalPragmas.forEach(pragma => {
      db.run(pragma, (err) => {
        if (err) {
          console.warn(`全局PRAGMA设置失败: ${pragma}`, err);
        }
        completed++;
        if (completed === total) {
          callback();
        }
      });
    });
  }

  /**
   * 设置连接级别的PRAGMA
   */
  setupConnectionPragmas(db, callback) {
    const connectionPragmas = [
      'PRAGMA cache_size = 10000',
      'PRAGMA temp_store = memory',
      'PRAGMA busy_timeout = 60000'
    ];

    let completed = 0;
    const total = connectionPragmas.length;

    connectionPragmas.forEach(pragma => {
      db.run(pragma, (err) => {
        if (err) {
          console.warn(`连接PRAGMA设置失败: ${pragma}`, err);
        }
        completed++;
        if (completed === total) {
          callback();
        }
      });
    });
  }

  /**
   * 完成连接设置
   */
  finalizeConnection(db, connectionId, resolve) {
    // 添加连接元数据
    this.connectionMetadata.set(db, {
      id: connectionId,
      createdAt: new Date(),
      lastUsed: new Date(),
      queryCount: 0,
      isHealthy: true
    });

    this.updateMetrics('connectionCreated');
    resolve(db);
  }

  /**
   * 清理过期连接
   */
  async cleanupExpiredConnections() {
    const now = new Date();
    const expiredConnections = [];

    for (let i = this.pool.length - 1; i >= 0; i--) {
      const connection = this.pool[i];
      const metadata = this.connectionMetadata.get(connection);

      if (metadata && (now - metadata.createdAt) > this.options.connectionMaxAge) {
        expiredConnections.push(connection);
        this.pool.splice(i, 1);
      }
    }

    // 关闭过期连接
    for (const connection of expiredConnections) {
      await this.closeConnection(connection);
    }

    if (expiredConnections.length > 0) {
      console.log(`清理了 ${expiredConnections.length} 个过期连接`);
    }
  }

  /**
   * 测试池中的连接
   */
  async testPoolConnections() {
    const unhealthyConnections = [];

    for (const connection of this.pool) {
      try {
        await this.testConnection(connection);
        const metadata = this.connectionMetadata.get(connection);
        if (metadata) {
          metadata.isHealthy = true;
        }
      } catch (error) {
        unhealthyConnections.push(connection);
        const metadata = this.connectionMetadata.get(connection);
        if (metadata) {
          metadata.isHealthy = false;
        }
      }
    }

    // 移除不健康的连接
    for (const connection of unhealthyConnections) {
      const index = this.pool.indexOf(connection);
      if (index > -1) {
        this.pool.splice(index, 1);
        await this.closeConnection(connection);
      }
    }

    if (unhealthyConnections.length > 0) {
      console.log(`移除了 ${unhealthyConnections.length} 个不健康的连接`);
    }
  }

  /**
   * 获取数据库连接
   */
  async getConnection() {
    const startTime = Date.now();

    return new Promise((resolve, reject) => {
      const timeout = setTimeout(() => {
        const waitingIndex = this.waitingQueue.findIndex(req => req.requestTime === startTime);
        if (waitingIndex > -1) {
          this.waitingQueue.splice(waitingIndex, 1);
        }
        reject(new Error(`获取数据库连接超时 (${this.options.acquireTimeout}ms)`));
      }, this.options.acquireTimeout);

      const processRequest = async () => {
        try {
          // 如果池中有可用连接
          if (this.pool.length > 0) {
            const connection = this.pool.pop();
            this.activeConnections++;

            // 测试连接是否仍然有效
            try {
              await this.testConnection(connection);

              // 更新连接元数据
              const metadata = this.connectionMetadata.get(connection);
              if (metadata) {
                metadata.lastUsed = new Date();
                metadata.queryCount++;
              }

              clearTimeout(timeout);
              resolve(connection);
              return;
            } catch (testError) {
              // 连接无效，关闭并创建新连接
              console.warn('检测到无效连接，正在创建新连接');
              await this.closeConnection(connection);
              this.activeConnections--;
            }
          }

          // 如果可以创建新连接
          if (this.activeConnections < this.options.maxConnections) {
            try {
              const connection = await Promise.race([
                this.createConnection(),
                new Promise((_, reject) =>
                  setTimeout(() => reject(new Error('连接创建超时')), this.options.connectionCreationTimeout)
                )
              ]);

              this.activeConnections++;
              clearTimeout(timeout);
              resolve(connection);
              return;
            } catch (createError) {
              console.error('创建连接失败:', createError.message);
              // 如果创建失败，尝试等待现有连接
            }
          }

          // 加入等待队列
          this.waitingQueue.push({
            resolve: (conn) => {
              clearTimeout(timeout);
              resolve(conn);
            },
            reject: (err) => {
              clearTimeout(timeout);
              reject(err);
            },
            timeout,
            requestTime: startTime
          });
        } catch (error) {
          clearTimeout(timeout);
          reject(error);
        }
      };

      processRequest();
    });
  }

  /**
   * 更新指标
   */
  updateMetrics(type, value = 1) {
    if (!this.options.enableMetrics) return;

    switch (type) {
      case 'querySuccess':
        this.metrics.totalQueries += value;
        this.metrics.successfulQueries += value;
        break;
      case 'queryFailed':
        this.metrics.totalQueries += value;
        this.metrics.failedQueries += value;
        break;
      case 'transactionSuccess':
        this.metrics.totalTransactions += value;
        this.metrics.successfulTransactions += value;
        break;
      case 'transactionFailed':
        this.metrics.totalTransactions += value;
        this.metrics.failedTransactions += value;
        break;
      case 'connectionCreated':
        this.metrics.connectionCreated += value;
        break;
      case 'connectionDestroyed':
        this.metrics.connectionDestroyed += value;
        break;
      case 'averageQueryTime':
        // 计算移动平均值
        this.metrics.averageQueryTime =
          (this.metrics.averageQueryTime * 0.9) + (value * 0.1);
        break;
    }
  }

  /**
   * 释放数据库连接
   */
  releaseConnection(connection) {
    this.activeConnections--;

    // 检查连接是否健康
    const metadata = this.connectionMetadata.get(connection);
    if (metadata && !metadata.isHealthy) {
      // 不健康的连接直接关闭
      this.closeConnection(connection);
      return;
    }

    // 如果有等待的请求
    if (this.waitingQueue.length > 0) {
      const waitingRequest = this.waitingQueue.shift();
      this.activeConnections++;

      // 更新连接元数据
      if (metadata) {
        metadata.lastUsed = new Date();
        metadata.queryCount++;
      }

      waitingRequest.resolve(connection);
    } else {
      // 放回连接池
      this.pool.push(connection);
    }
  }

  /**
   * 关闭单个连接
   */
  async closeConnection(connection) {
    return new Promise((resolve) => {
      // 清理元数据
      this.connectionMetadata.delete(connection);
      this.updateMetrics('connectionDestroyed');

      connection.close((err) => {
        if (err) {
          console.error('关闭数据库连接失败:', err);
        }
        resolve();
      });
    });
  }

  /**
   * 执行查询（带重试机制和性能监控）
   */
  async query(sql, params = []) {
    const startTime = Date.now();
    let lastError;

    for (let attempt = 1; attempt <= this.options.retryAttempts; attempt++) {
      let connection;

      try {
        connection = await this.getConnection();

        const result = await new Promise((resolve, reject) => {
          const _q = sql.trim().toLowerCase();
          const isSelect = _q.startsWith('select') || _q.startsWith('pragma') || _q.startsWith('explain');

          if (isSelect) {
            connection.all(sql, params, (err, rows) => {
              if (err) {
                reject(err);
              } else {
                resolve(rows);
              }
            });
          } else {
            connection.run(sql, params, function(err) {
              if (err) {
                reject(err);
              } else {
                resolve({
                  lastInsertRowid: this.lastID,
                  changes: this.changes
                });
              }
            });
          }
        });

        // 记录成功指标
        const queryTime = Date.now() - startTime;
        this.updateMetrics('querySuccess');
        this.updateMetrics('averageQueryTime', queryTime);

        return result;
      } catch (error) {
        lastError = error;
        const attemptText = `尝试 ${attempt}/${this.options.retryAttempts}`;
        console.error('数据库操作失败', attemptText, ':', error.message);

        // 对于约束错误（如唯一约束），不需要重试，立即返回
        if (error.message.includes('UNIQUE constraint failed') ||
            error.message.includes('CONSTRAINT') ||
            error.code === 'SQLITE_CONSTRAINT') {
          throw error; // 立即抛出，不重试
        }

        if (attempt < this.options.retryAttempts) {
          await this.delay(this.options.retryDelay * attempt);
        }
      } finally {
        if (connection) {
          this.releaseConnection(connection);
        }
      }
    }

    // 记录失败指标
    this.updateMetrics('queryFailed');
    this.emit('queryFailed', { sql, params, error: lastError });

    throw lastError;
  }

  /**
   * 执行事务（增强版本）
   */
  async transaction(operations) {
    const startTime = Date.now();
    let connection;

    try {
      connection = await this.getConnection();

      // 开始事务
      await new Promise((resolve, reject) => {
        connection.run('BEGIN IMMEDIATE TRANSACTION', (err) => {
          if (err) reject(err);
          else resolve();
        });
      });

      const results = [];

      // 执行所有操作
      for (const operation of operations) {
        const result = await new Promise((resolve, reject) => {
          const { sql, params = [] } = operation;
          const isSelect = sql.trim().toLowerCase().startsWith('select');

          if (isSelect) {
            connection.all(sql, params, (err, rows) => {
              if (err) reject(err);
              else resolve(rows);
            });
          } else {
            connection.run(sql, params, function(err) {
              if (err) reject(err);
              else resolve({
                lastInsertRowid: this.lastID,
                changes: this.changes
              });
            });
          }
        });

        results.push(result);
      }

      // 提交事务
      await new Promise((resolve, reject) => {
        connection.run('COMMIT', (err) => {
          if (err) reject(err);
          else resolve();
        });
      });

      // 记录成功指标
      const transactionTime = Date.now() - startTime;
      this.updateMetrics('transactionSuccess');
      this.emit('transactionCompleted', {
        operationCount: operations.length,
        duration: transactionTime
      });

      return results;
    } catch (error) {
      // 回滚事务
      if (connection) {
        await new Promise((resolve) => {
          connection.run('ROLLBACK', () => resolve());
        });
      }

      // 记录失败指标
      this.updateMetrics('transactionFailed');
      this.emit('transactionFailed', {
        operations,
        error,
        duration: Date.now() - startTime
      });

      throw error;
    } finally {
      if (connection) {
        this.releaseConnection(connection);
      }
    }
  }

  /**
   * 延迟函数
   */
  delay(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
  }

  /**
   * 关闭连接池
   */
  async close() {
    console.log('关闭数据库连接池...');

    // 停止健康检查
    if (this.healthCheckTimer) {
      clearInterval(this.healthCheckTimer);
      this.healthCheckTimer = null;
    }

    // 拒绝所有等待的请求
    while (this.waitingQueue.length > 0) {
      const waitingRequest = this.waitingQueue.shift();
      waitingRequest.reject(new Error('连接池正在关闭'));
    }

    // 关闭所有连接
    const allConnections = [...this.pool];
    this.pool = [];

    for (const connection of allConnections) {
      await this.closeConnection(connection);
    }

    // 清理元数据
    this.connectionMetadata.clear();

    console.log('数据库连接池已关闭');
    this.emit('poolClosed');
  }

  /**
   * 获取连接池详细状态
   */
  getStatus() {
    const now = new Date();
    const connectionAges = [];

    for (const [, metadata] of this.connectionMetadata.entries()) {
      connectionAges.push({
        id: metadata.id,
        age: now - metadata.createdAt,
        lastUsed: now - metadata.lastUsed,
        queryCount: metadata.queryCount,
        isHealthy: metadata.isHealthy
      });
    }

    return {
      poolSize: this.pool.length,
      activeConnections: this.activeConnections,
      waitingQueue: this.waitingQueue.length,
      maxConnections: this.options.maxConnections,
      totalConnections: this.connectionMetadata.size,
      connectionAges,
      metrics: this.options.enableMetrics ? { ...this.metrics } : null,
      uptime: Date.now() - this.startTime,
      isHealthy: this.pool.length > 0 || this.activeConnections > 0
    };
  }

  /**
   * 获取性能指标
   */
  getMetrics() {
    if (!this.options.enableMetrics) {
      return null;
    }

    const successRate = this.metrics.totalQueries > 0
      ? (this.metrics.successfulQueries / this.metrics.totalQueries) * 100
      : 0;

    const transactionSuccessRate = this.metrics.totalTransactions > 0
      ? (this.metrics.successfulTransactions / this.metrics.totalTransactions) * 100
      : 0;

    return {
      ...this.metrics,
      successRate: Math.round(successRate * 100) / 100,
      transactionSuccessRate: Math.round(transactionSuccessRate * 100) / 100,
      averageQueryTime: Math.round(this.metrics.averageQueryTime * 100) / 100
    };
  }

  /**
   * 重置指标
   */
  resetMetrics() {
    this.metrics = {
      totalQueries: 0,
      successfulQueries: 0,
      failedQueries: 0,
      totalTransactions: 0,
      successfulTransactions: 0,
      failedTransactions: 0,
      averageQueryTime: 0,
      connectionCreated: 0,
      connectionDestroyed: 0
    };
    this.startTime = Date.now();
  }
}

module.exports = DatabasePool;
